package com.loto.webapp.dao.impl.manage;

import java.util.Date;
import java.util.List;

import org.hibernate.Session;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import org.springframework.transaction.annotation.Transactional;

import com.loto.webapp.dao.manage.FeeFlowDao;
import com.loto.webapp.model.Dict.FeeFlowType;
import com.loto.webapp.model.Dict.RefType;
import com.loto.webapp.model.FeeFlow;

@Transactional
public class FeeFlowDaoImpl extends HibernateDaoSupport implements FeeFlowDao {
	public List<FeeFlow> countFeeGroupByTypeByDate(Date beginDate, Date endDate) {
		Session session = getSessionFactory().getCurrentSession();
		session.beginTransaction();

		// SimpleDateFormat f = new SimpleDateFormat("yyyy-MM-dd");
		// List<FeeFlow> feeFlowList = new ArrayList<FeeFlow>();

		List<FeeFlow> feeFlowList = session
				.createQuery(
						"select new FeeFlow(feeFlowTypeSid,sum(feeFlowChanged)) from com.loto.webapp.model.FeeFlow where feeFlowSucFlg=1 AND feeFlowTime between :sdate and :edate group by feeFlowTypeSid")
				.setParameter("sdate", beginDate)
				.setParameter("edate", endDate).list();

		/*
		 * List<FeeFlow> feeFlowList = getHibernateTemplate() .find(
		 * "select new FeeFlow(feeFlowId,feeFlowTypeSid) from com.loto.webapp.model.FeeFlow where FEE_FLOW_TIME between ? and ?"
		 * , beginDate, endDate);
		 */
		/*
		 * feeFlowList = session .createSQLQuery(
		 * "select FEE_FLOW_TYPE_SID,sum(FEE_FLOW_CHANGED) as FEE_FLOW_CHANGED from IBA_FEE_FLOW where FEE_FLOW_TIME between :sdate and :edate group by FEE_FLOW_TYPE_SID"
		 * ) .addEntity(FeeFlow.class).setParameter("sdate", beginDate)
		 * .setParameter("edate", endDate).list();
		 */

		// SimpleDateFormat f = new SimpleDateFormat("yyyy-MM-dd");

		/*
		 * @SuppressWarnings("unchecked") List<FeeFlow> feeFlowList =
		 * getHibernateTemplate() .find(
		 * "select f.feeFlowTypeSid,sum(f.feeFlowChanged) f.feeFlowChanged from com.loto.webapp.model.FeeFlow f where f.feeFlowTime between '"
		 * + f.format(beginDate) + "' and '" + f.format(endDate) +
		 * "' group by f.feeFlowTypeSid");
		 */
		session.getTransaction().commit();
		/*
		 * for (Object result : results) { FeeFlow fl = new FeeFlow();
		 * fl.setFeeFlowTypeSid(((FeeFlow)result).getFeeFlowTypeSid());
		 * fl.setFeeFlowChanged(((FeeFlow)result).getFeeFlowChanged());
		 * feeFlowList.add(fl); }
		 */
		return feeFlowList;
	}

	public List<FeeFlow> countFeeGroupByTypeByDateAndRef(Date beginDate,
			Date endDate, RefType refType) {
		Session session = getSessionFactory().getCurrentSession();
		session.beginTransaction();

		String typeSql = "";
		switch (refType) {
		case GOODS:
			typeSql = " AND feeFlowRefFid IN(SELECT goodsId FROM com.loto.webapp.model.GoodsInfo)";
			break;
		case PACK:
			typeSql = " AND feeFlowRefFid IN(SELECT packId FROM com.loto.webapp.model.PackInfo)";
			break;
		case OTHERS:
			typeSql = " AND feeFlowRefFid=0";
			break;
		}

		List<FeeFlow> feeFlowList = session
				.createQuery(
						"SELECT new FeeFlow(feeFlowTypeSid,sum(feeFlowChanged)) FROM com.loto.webapp.model.FeeFlow WHERE feeFlowSucFlg=1 AND feeFlowTime BETWEEN :sdate AND :edate "
								+ typeSql + " GROUP BY feeFlowTypeSid")
				.setParameter("sdate", beginDate)
				.setParameter("edate", endDate).list();

		session.getTransaction().commit();

		return feeFlowList;
	}

	public List<FeeFlow> getFeeFlowDetailByDate(Date beginDate, Date endDate,
			List<FeeFlowType> feeFlowTypeList) {
		if (feeFlowTypeList.size() == 0)
			return null;
		Session session = getSessionFactory().getCurrentSession();
		session.beginTransaction();

		String typeSql = "";
		for (FeeFlowType f : feeFlowTypeList) {
			typeSql += f.getIndex() + ",";
		}
		typeSql = typeSql.substring(0, typeSql.length() - 1);
		typeSql = " AND feeFlowTypeSid IN(" + typeSql + ")";
		/*
		 * switch (refType) { case GOODS: typeSql =
		 * " AND feeFlowRefFid IN(SELECT goodsId FROM com.loto.webapp.model.GoodsInfo)"
		 * ; break; case PACK: typeSql =
		 * " AND feeFlowRefFid IN(SELECT packId FROM com.loto.webapp.model.PackInfo)"
		 * ; break; case OTHERS: typeSql = " AND feeFlowRefFid=0"; break; }
		 */

		// select sum(fee_flow_changed) from iba_fee_flow where
		// FEE_FLOW_TYPE_SID IN(6) AND FEE_FLOW_SUC_FLG=1 and fee_flow_time
		// between '2011-9-1' and '2011-10-1'

		List<FeeFlow> feeFlowList = session
				.createQuery(
						"SELECT new FeeFlow(feeFlowChanged,feeFlowPayNo,feeFlowMemo,feeFlowTime) FROM com.loto.webapp.model.FeeFlow WHERE feeFlowSucFlg=1 AND feeFlowTime BETWEEN :sdate AND :edate"
								+ typeSql + " ORDER BY feeFlowTime DESC")
				.setParameter("sdate", beginDate)
				.setParameter("edate", endDate).list();

		session.getTransaction().commit();

		return feeFlowList;
	}

	public List<FeeFlow> getFeeFlowDetailByDateAndRef(Date beginDate,
			Date endDate, List<FeeFlowType> feeFlowTypeList, RefType refType) {
		if (feeFlowTypeList.size() == 0)
			return null;
		Session session = getSessionFactory().getCurrentSession();
		session.beginTransaction();

		String typeSql = "";
		for (FeeFlowType f : feeFlowTypeList) {
			typeSql += f.getIndex() + ",";
		}
		typeSql = typeSql.substring(0, typeSql.length() - 1);
		typeSql = " AND feeFlowTypeSid IN(" + typeSql + ")";
		switch (refType) {
		case GOODS:
			typeSql += " AND feeFlowRefFid IN(SELECT goodsId FROM com.loto.webapp.model.GoodsInfo)";
			break;
		case PACK:
			typeSql += " AND feeFlowRefFid IN(SELECT packId FROM com.loto.webapp.model.PackInfo)";
			break;
		case OTHERS:
			typeSql += " AND feeFlowRefFid=0";
			break;
		}

		// select sum(fee_flow_changed) from iba_fee_flow where
		// FEE_FLOW_TYPE_SID IN(6) AND FEE_FLOW_SUC_FLG=1 and fee_flow_time
		// between '2011-9-1' and '2011-10-1'

		List<FeeFlow> feeFlowList = session
				.createQuery(
						"SELECT new FeeFlow(feeFlowChanged,feeFlowPayNo,feeFlowMemo,feeFlowTime) FROM com.loto.webapp.model.FeeFlow WHERE feeFlowSucFlg=1 AND feeFlowTime BETWEEN :sdate AND :edate"
								+ typeSql + " ORDER BY feeFlowTime DESC")
				.setParameter("sdate", beginDate)
				.setParameter("edate", endDate).list();

		session.getTransaction().commit();

		return feeFlowList;
	}

}
